package com.kexio.gen.service.impl;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Set;

import javax.sql.DataSource;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.kexio.gen.domain.entity.GenTableColumn;
import com.kexio.gen.mapper.GenTableColumnMapper;
import com.kexio.gen.service.GenTableColumnService;

@Service
public class GenTableColumnServiceImpl extends ServiceImpl<GenTableColumnMapper, GenTableColumn> implements GenTableColumnService {

    private final DataSource dataSource;

    public GenTableColumnServiceImpl(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public int introspectAndMerge(Long tableId, String tableName) {
        // 读取已存在列，避免覆盖
        List<GenTableColumn> existing = this.list(new QueryWrapper<GenTableColumn>().eq("table_id", tableId));
        Set<String> existingNames = new HashSet<>();
        for (GenTableColumn col : existing) {
            if (col.getColumnName() != null) {
                existingNames.add(col.getColumnName().toLowerCase(Locale.ROOT));
            }
        }

        List<GenTableColumn> toInsert = new ArrayList<>();
        try (Connection conn = dataSource.getConnection()) {
            String schema = safeSchema(conn);
            DatabaseMetaData meta = conn.getMetaData();
            try (ResultSet rs = meta.getColumns(conn.getCatalog(), schema, tableName, null)) {
                while (rs.next()) {
                    String colName = rs.getString("COLUMN_NAME");
                    if (colName == null) {
                        continue;
                    }
                    String key = colName.toLowerCase(Locale.ROOT);
                    if (existingNames.contains(key)) {
                        continue;
                    }
                    GenTableColumn c = new GenTableColumn();
                    c.setTableId(tableId);
                    c.setColumnName(colName);
                    c.setPropertyName(snakeToCamel(colName));
                    c.setJdbcType(rs.getString("TYPE_NAME"));
                    c.setJavaType(mapToJavaType(rs.getInt("DATA_TYPE"), rs.getString("TYPE_NAME")));
                    c.setLength(safeInt(rs, "COLUMN_SIZE"));
                    c.setScale(safeInt(rs, "DECIMAL_DIGITS"));
                    c.setNullable("YES".equalsIgnoreCase(rs.getString("IS_NULLABLE")));
                    c.setDefaultValue(rs.getString("COLUMN_DEF"));
                    c.setComment(rs.getString("REMARKS"));
                    toInsert.add(c);
                }
            }
        } catch (Exception e) {
            // 失败不抛出详细堆栈到外部，保持事务一致性
            throw new RuntimeException("Introspect columns failed for table: " + tableName, e);
        }

        if (!toInsert.isEmpty()) {
            this.saveBatch(toInsert);
        }
        return toInsert.size();
    }

    private String safeSchema(Connection conn) {
        try {
            String schema = conn.getSchema();
            if (schema == null || schema.isEmpty()) {
                return null; // 交给驱动默认
            }
            return schema;
        } catch (Exception e) {
            return null;
        }
    }

    private Integer safeInt(ResultSet rs, String column) {
        try {
            int v = rs.getInt(column);
            return rs.wasNull() ? null : v;
        } catch (Exception e) {
            return null;
        }
    }

    private String snakeToCamel(String name) {
        if (name == null || name.isEmpty()) return name;
        StringBuilder sb = new StringBuilder();
        boolean upperNext = false;
        for (int i = 0; i < name.length(); i++) {
            char ch = name.charAt(i);
            if (ch == '_' || ch == '-') {
                upperNext = true;
            } else if (upperNext) {
                sb.append(Character.toUpperCase(ch));
                upperNext = false;
            } else {
                sb.append(Character.toLowerCase(ch));
            }
        }
        return sb.toString();
    }

    private String mapToJavaType(int jdbcTypeCode, String typeName) {
        // 简化类型映射（PostgreSQL为主，通用回退到 String）
        // jdbcTypeCode 参照 java.sql.Types
        Map<Integer, String> basic = new HashMap<>();
        basic.put(java.sql.Types.BOOLEAN, Boolean.class.getName());
        basic.put(java.sql.Types.BIT, Boolean.class.getName());
        basic.put(java.sql.Types.TINYINT, Integer.class.getName());
        basic.put(java.sql.Types.SMALLINT, Integer.class.getName());
        basic.put(java.sql.Types.INTEGER, Integer.class.getName());
        basic.put(java.sql.Types.BIGINT, Long.class.getName());
        basic.put(java.sql.Types.FLOAT, Double.class.getName());
        basic.put(java.sql.Types.REAL, Double.class.getName());
        basic.put(java.sql.Types.DOUBLE, Double.class.getName());
        basic.put(java.sql.Types.DECIMAL, java.math.BigDecimal.class.getName());
        basic.put(java.sql.Types.NUMERIC, java.math.BigDecimal.class.getName());
        basic.put(java.sql.Types.DATE, java.time.LocalDate.class.getName());
        basic.put(java.sql.Types.TIME, java.time.LocalTime.class.getName());
        basic.put(java.sql.Types.TIMESTAMP, java.time.LocalDateTime.class.getName());
        basic.put(java.sql.Types.TIMESTAMP_WITH_TIMEZONE, java.time.OffsetDateTime.class.getName());
        basic.put(java.sql.Types.VARCHAR, String.class.getName());
        basic.put(java.sql.Types.CHAR, String.class.getName());
        basic.put(java.sql.Types.LONGVARCHAR, String.class.getName());
        basic.put(java.sql.Types.CLOB, String.class.getName());
        basic.put(java.sql.Types.OTHER, String.class.getName());

        String mapped = basic.get(jdbcTypeCode);
        if (mapped != null) {
            return mapped;
        }
        // 补充常见 PostgreSQL 专有（类型码可能为 OTHER）
        if (typeName != null) {
            String t = typeName.toLowerCase(Locale.ROOT);
            if (t.contains("json")) return String.class.getName();
            if (t.contains("uuid")) return java.util.UUID.class.getName();
            if (t.contains("bytea")) return "byte[]";
        }
        return String.class.getName();
    }
}


